﻿using FirstHomeWork.Interface;
using FirstHomeWork.Model;
using FirstHomeWork.Utility;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;

namespace FirstHomeWork.SqlServer
{
    public class SqlServerHelper : IDBHelper
    {
        public static readonly string Customers = CustomConfig.GetConfig("SqlServerConnStr");


        /// <summary>
        /// 添加实体信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Add<T>(T t) where T : BaseModel, new()
        {
            try
            {
                Type type = typeof(T);
                var parameters = type.GetProperties().Where(p => !p.Name.Equals("Id")).Select(p =>
                new SqlParameter()
                {
                    ParameterName = $"@{p.Name}",
                    SqlValue = $"{p.GetValue(t)}"
                });
                string sql = SqlCache<T>.AddSql;
                return ExecuteNonQuery(parameters, sql);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 根据主键删除sql信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete<T>(int id) where T : BaseModel, new()
        {
            Type type = typeof(T);
            try
            {
                var parameters = type.GetProperties().Where(p => p.Name.Equals("Id")).Select(p =>
                new SqlParameter()
                {
                    ParameterName = $"@{p.Name}",
                    SqlValue = $"{id}"
                });
                string sql = SqlCache<T>.DeleteSql;
                return ExecuteNonQuery(parameters, sql);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        /// <summary>
        /// 根据主键编号查询单条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Find<T>(int id) where T : BaseModel, new()
        {
            Type type = typeof(T);
            T t = new T();
            try
            {
                var parameters = type.GetProperties().Where(p => p.Name.Equals("Id")).Select(p =>
                new SqlParameter()
                {
                    ParameterName = $"@{p.Name}",
                    SqlValue = $"{id}"
                });
                string sql = SqlCache<T>.FindSql;
                using (SqlConnection connection = new SqlConnection(Customers))
                {
                    SqlCommand sqlCommand = new SqlCommand(sql, connection);

                    sqlCommand.Parameters.AddRange(parameters.ToArray());
                    connection.Open();
                    var reader = sqlCommand.ExecuteReader();
                    if (reader.Read())
                    {

                        foreach (PropertyInfo property in type.GetProperties())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                //属性名与查询出来的列名比较
                                if (property.Name.ToLower() != reader.GetName(i).ToLower()) continue;
                                var kvalue = reader[property.Name];
                                if (kvalue == DBNull.Value) continue;
                                property.SetValue(t, kvalue, null);
                                break;
                            }
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return t;
        }

        /// <summary>
        /// 根据参数查询全部数据集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> Query<T>() where T : BaseModel, new()
        {
            Type type = typeof(T);

            List<T> list = new List<T>();
            try
            {
                string sql = SqlCache<T>.QuerySql;
                using (SqlConnection connection = new SqlConnection(Customers))
                {
                    SqlCommand sqlCommand = new SqlCommand(sql, connection);

                    
                    connection.Open();
                    var reader = sqlCommand.ExecuteReader();
                    while (reader.Read())
                    {
                        T t = new T();
                        foreach (PropertyInfo property in type.GetProperties())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                
                                //属性名与查询出来的列名比较
                                if (property.Name.ToLower() != reader.GetName(i).ToLower()) continue;
                                var kvalue = reader[property.Name];
                                if (kvalue == DBNull.Value) continue;
                                property.SetValue(t, kvalue, null);

                                
                                break;
                            }
                            
                        }
                        list.Add(t);
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return list;
        }

        /// <summary>
        /// 根据参数修改数据信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool Update<T>(T t) where T : BaseModel, new()
        {
            try
            {
                Type type = typeof(T);
                var parameters = type.GetProperties().Select(p =>
                new SqlParameter()
                {
                    ParameterName = $"@{p.Name}",
                    SqlValue = $"{p.GetValue(t)}"
                });
                string sql = SqlCache<T>.UpdateSql;
                return ExecuteNonQuery(parameters, sql);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        private static bool ExecuteNonQuery(IEnumerable<SqlParameter> parameters, string sql)
        {
            using (SqlConnection connection = new SqlConnection(Customers))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, connection);

                sqlCommand.Parameters.AddRange(parameters.ToArray());
                connection.Open();
                return sqlCommand.ExecuteNonQuery() > 0;
            }
        }

        public IList<T> QueryWhere<T>(string sqlWhere) where T : new()
        {
            Type type = typeof(T);
            List<T> list = new List<T>();
            using (SqlConnection conn = new SqlConnection(Customers))
            {
                //如果连接通道未打开 则打开通道
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                //为命令对象设置连接通道
                SqlCommand cmd = new SqlCommand(sqlWhere, conn);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    foreach (var item in type.GetProperties())
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            if (item.Name.ToLower() != dr.GetName(i).ToLower()) continue;
                            var kvalue = dr[item.Name];
                            if (kvalue == DBNull.Value) continue;
                            item.SetValue(t, kvalue, null);
                            break;
                        }
                    }
                    list.Add(t);
                }
            }
            return list;
        }



        /// <summary>
        /// 获取所有的表名称
        /// </summary>
        /// <returns></returns>
        public List<TableModel> GetAllTable()
        {
            string SqlString = @"select ROW_NUMBER() OVER (ORDER BY a.name) AS Code, a.name AS Table_Name,CONVERT(NVARCHAR(100),isnull(g.[value],'')) AS Table_Explain
from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)";
            return QueryWhere<TableModel>(SqlString).ToList();
        }

        /// <summary>
        /// 根据表名称 获取列
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<Tables_ColumnsModel> GetColsByTableName(string tableName)
        {
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(tableName))
            {
                sqlWhere = $"where d.name='{tableName}'";
            }

            string SqlString = $@"

SELECT 
    d.name AS TabName,
	isnull(f.value,'') AS TabNameRemark,
	convert(int,a.colorder) AS ColOrder,
	a.name AS ColName,
	case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end ColIsIdentity,
	case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end ColIsKey,
	b.name AS ColType,
	convert(int,a.length) AS ColLength,
	convert(int,COLUMNPROPERTY(a.id,a.name,'PRECISION')) AS ColMaxLength,
	convert(int,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)) AS ColScale,
	case when a.isnullable=1 then 1 else 0 end ColIsNull,
	isnull(e.text,'') AS ColDefaultValue,
    isnull(g.[value],'') AS ColRemark
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id  
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
{sqlWhere}
order by a.id,a.colorder
";
            return QueryWhere<Tables_ColumnsModel>(SqlString).ToList();
            //return await this.TABLES_COLUMNS_DbSet.FromSqlRaw(SqlString).ToListAsync();
        }
    }
}
